from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)


#数据库的变量
HOST = '127.0.0.1'
PORT = 3306
DATABASE = 'itbz'
USER = 'root'
PWD = 'root'
DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATABASE}'

app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI

#链接数据库
db = SQLAlchemy(app)

#创建模型类
class User(db.Model):
    __tablename__ = 't_user'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    name = db.Column(db.String(32))

    def __repr__(self):
        return f'<user id={self.id} name={self.name}'


class Newss(db.Model):
    __tablename__ = 't_news'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    content = db.Column(db.String(100))
    uid = db.Column(db.Integer,db.ForeignKey('t_user.id'))

    user = db.relationship('User',backref='newss')

    def __repr__(self):
        return f'<news id={self.id} content={self.content}'

#创建表
# with app.app_context():
#     db.create_all()

#增加数据
def create_data():
    with app.app_context():
        user = User(name='百战')
        news = Newss(content='python内容')
        user.newss.append(news)
        db.session.add(user)
        db.session.commit()

#查询单表数据
def query_data_one():
    with app.app_context():
        users = User.query.all()
        print(users)

#查询多表数据
def query_data_many():
    with app.app_context():
        user= db.session.query(User,Newss.content).join(Newss,Newss.uid==User.id).all()
        print(user)

#修改数据
def update_data():
    with app.app_context():
        user = User.query.first()
        user.name = '百战程序员'
        db.session.commit()

#删除数据
def delete_data():
    with app.app_context():
        news = Newss.query.first()
        db.session.delete(news)
        db.session.commit()

if __name__ == '__main__':
    # create_data()
    # query_data_one()
    # query_data_many()
    # update_data()
    delete_data()